Example: Using the Batch Processor with a Database

Example: Use the Batch Processor with a database

This example demonstrates how to run the Oracle Policy Automation Batch Processor read data from database tables, and writing the values back to the database.

Note: Currently, only the Java version of the Batch Processor can read and write to the database, so this example can be run for the Java Batch Processor only.

The database

This example uses the sample "HR" database for Oracle 10g or 11g. You can easily create this schema by ensuring that the Sample Schemas option is turned on when creating a new database via the Oracle Database Configuration Assistant.

SQL scripts are provided to create (and also remove) some extra tables; these scripts do the following:

Create some extra tables in the HR schema to hold Batch Processor return data OPA_EMPLOYEES, OPA_LOCATIONS and OPA_DEPARTMENTS. These tables each have a single column and a foreign key back to the original table.
Create some extra views to join the OPA tables to the original tables (OPA_EMPLOYEES_VIEW, OPA_LOCATIONS_VIEW, OPA_DEPARTMENTS_VIEW).
Populate each created table with an empty reference for each row in its corresponding HR table.

The Batch Processor reads data from the created views and writes the output data back to these views. This technique of combining tables through views and then having the Batch Processor read and write to this view, is useful as it allows you to write Batch Processor output to a dedicated table without affecting the original table.

It is not necessary to read and write to a view, but this allows us to demonstrate a Batch Processor which does not affect the original source tables.

Set up the database for the Batch Processor run

In order to run this example, the extra tables and views described above are required. An sqlplus script is provided to create the necessary objects. To run the SQL scripts, do the following:

Open a command prompt and go to the examples/determinations-batch/HRDatabase directory.
Start sqlplus and connect to the database with the sample "HR" schema using a user with permissions to create tables and views (for example, SYS as SYSDBA).
Execute the script hr_create_tables.sql by typing @hr_create_tables.sql <return>.
Run the Batch Processor.

Also, you need to ensure that the user "HR" is unlocked. This user is locked by default in the sample database. The configuration file assumes that password for the user HR is "HR". To unlock the HR user, do the following:

Start sqlplus and connect to the same database as above.
Execute the following command: alter user HR account unlock identified by HR;<return>.

This command will unlock the user HR and set the password for this user to HR. If you choose to use another password, you will need to modify the database url in the configuration.

From a command prompt in the examples/determinations-batch/HRDatabase directory, execute the Batch Processor with the following command:

java -jar ../../../engine/determinations-batch.jar

The batch process will run, executing across the data in the database.The results will be written back to the database.

Looking at the Batch Processor results

The Batch Processor should successfully complete with a statement such as: Finishing batch processor. Cases processed: 23 ...

Each case consists of a row from the OPA_LOCATIONS_VIEW view, and related rows from the OPA_DEPARTMENTS_VIEW and OPA_EMPLOYEES_VIEW views.

The results are written back to these views, but are also written to the OPA_LOCATIONS, OPA_DEPARTMENTS and OPA_EMPLOYEES tables.

For each row of each table there is a single output value as shown in the following table:

table	updated column	description of output
opa_location	total_salary	the total salaries for the location (the sum of all the departments total salaries)
opa_department	total_salary	the total salaries for the department (the sum of all the employee salaries)
opa_employee	long_service_date	the date at which an employee is eligible for long service leave, based on their hire date

You can view the values written back to the table by looking at the views or the Oracle Policy Automation tables; for example:

SELECT * FROM HR.OPA_EMPLOYEES_VIEW;

will return all the data for employees, including the long_service_date column.

Looking at the rules

You can look at the rulebase used by this Batch Processor example, by going to the rulebase project located at:
examples/rulebases/source/human-resources.zip.

If you unzip and open the project, and examine the Rules.doc document, you will see the simple rules which calculate the Batch Processor output.

The data model can be found in the properties.xsrc file.

Looking at the configuration file

The file config.xml is automatically picked up by the Batch Processor because it is called config.xml and it is in the working directory. The configuration file contains the mappings from the tables to Oracle Policy Automation data and also the run options for the Batch Processor.

Options

<options>
  <base>opa_locations_view</base>
  <rulebase>../../rulebases/compiled/human-resources.zip</rulebase>
  <database>
    <url>jdbc:oracle:thin:HR/HR@localhost:1521:xe</url>
      <driver>oracle.jdbc.OracleDriver</driver>
      <driversrc>.\lib\ojdbc5.jar</driversrc>
  </database>
  <output type="db" />
</options>

The options above specify how the Batch Processor will run, including the database connection information. In this section you can see the base table, the path to the rulebase used, the database connection information (including url, driver and driver source) and the output specification (to the database).

Mappings

<mappings>
  <mapping entity="global" table="opa_locations_view" primary-key="location_id">
    <attribute name="street-address" field="street_address" />
    <attribute name="city" field="city" />
    <attribute name="postal-code" field="postal_code" />
    <attribute name="state-province" field="state_province" />
    <attribute name="location-total-salary-cost" field="total_salary" output="true" />
  </mapping>
  
<mapping entity="department" table="opa_departments_view" primary-key="department_id">
    <attribute name="department-name" field="department_name" />
    <attribute name="department-total-salary-cost" field="total_salary" output="true" />
    <relationship name="all-departments" source-entity="global" foreign-key="location_id" />
</mapping> 

<mapping entity="employee" table="opa_employees_view" primary-key="employee_id">
    <attribute name="first-name" field="first_name" />
    <attribute name="last-name" field="last_name" />
    <attribute name="hire-date" field="hire_date" />
    <attribute name="salary" field="salary" />
    <attribute name="commission-pct" field="commission_pct" />
    <attribute name="long-service-date" field="long_service_date" output="true"/>
    <relationship name="all-employees" source-entity="department" foreign-key="department_id" />
  </mapping>
</mappings>

The mappings above specify how the database tables will be translated to the rulebase data model. In this section you can see the entities are mapped to tables, with entity attributes mapped to columns of the relevant tables. Relationships between the entities, including containment, are specified by the foreign key relationships between the tables; for example:

The entity employee is obtained from the table view opa_employees_view, and the entity attributes first-name, last-name and so on are all mapped to columns in the table.
The containment relationship all-employees is specified by the foreign key relationship between department and employee using the department_id column of the employee table. This column is a reference to the department that the employee belongs to.

Removing the Batch Processor tables and views

When you have finished with this example, you may want to remove the extra tables and views created; to do this:

Open a command prompt and go to the examples/determinations-batch/HRDatabase directory.
Start sqlplus.
Connect to the database with the sample "HR" schema using a user with permissions to create tables and views; for example, SYS as SYSDBA.
Execute the script hr_remove_tables.sql by typing: @hr_remove_tables.sql <return>.

This should remove the following tables and views: OPA_EMPLOYEES, OPA_LOCATIONS, OPA_DEPARTMENTS, OPA_EMPLOYEES_VIEW, OPA_LOCATIONS_VIEW, and OPA_DEPARTMENTS_VIEW.